﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Collections;
using System.Data.SqlClient;
using System.Xml;
using System.Xml.Schema;

namespace XML_Programming_Assignment_Thandar_.Class
{
    class DBManager
    {
        public const string DBCONNSTR = "server=localhost;database=Supplier1Database;integrated security=true";
        public static SqlConnection conn = new SqlConnection();
        public static SqlCommand comm = new SqlCommand();
        static bool checkValid = false;
        static ArrayList RFQList = new ArrayList();
        static ArrayList RFQItemList = new ArrayList();
        
        //method to read xml file based on the selected file path by the user
        public static ArrayList ReadXML(string filepath)
        {
            //clean the data inside the arraylist
            RFQList.Clear();
            RFQItemList.Clear();
            //declare xmlreader to read xmlfile
            XmlReader reader = XmlReader.Create(filepath);

            int rfqid;
            string rfqDate;
            int buyerid;

            string itemid;
            int quantity;
            double unitPrice;
            string note;
            try
            {
                //read xml file by using SAX
                while (reader.ReadToFollowing("RFQ"))
                {
                    reader.MoveToFirstAttribute();
                    rfqid = Convert.ToInt32(reader.Value);

                    reader.ReadToFollowing("Date");
                    rfqDate = reader.ReadElementContentAsString();


                    reader.ReadToFollowing("BuyerID");
                    buyerid = reader.ReadElementContentAsInt();

                    reader.ReadToFollowing("Notes");
                    note = reader.ReadElementContentAsString();

                    while (reader.ReadToFollowing("Item"))
                    {
                        reader.MoveToFirstAttribute();
                        itemid = reader.Value;

                        reader.ReadToFollowing("Quantities");
                        quantity = reader.ReadElementContentAsInt();

                        reader.ReadToFollowing("UnitPrice");
                        unitPrice = reader.ReadElementContentAsDouble();

                        RFQ_ItemDetail item = new RFQ_ItemDetail(rfqid, itemid, quantity, unitPrice);
                        RFQItemList.Add(item);
                    }
                    //insert data into rfq object
                    RFQ rfq = new RFQ(rfqid, buyerid, rfqDate, note);
                    //insert rfq object into arraylist
                    RFQList.Add(rfq);
                }
            }
            catch(Exception exception)
            {   
                MessageBox.Show(exception.Message);
            }    
            //return the rfqlist with its data
            return RFQList;
        }

        //method to save the rfq after reading it
        public static void SaveRFQ()
        {
            try
            {
                conn.ConnectionString = DBCONNSTR;
                conn.Open();
                comm.Connection = conn;

                //build sql query to perform commands for Database
                string query1 = "insert into RFQ (RFQID, CustomerID, RFQDate, Instructions) values (@rfq_id,@customer_id,@rfqdate,@instructions);";
                string query2 = "insert into RFQDetail (RFQID, ItemID, Quantity, AskingPrice) Values (@rfqID, @itemID, @quantity, @unit_price);";

                //execute the query 1
                foreach (RFQ request in RFQList)
                {
                    SqlCommand command = new SqlCommand(query1, conn);
                    command.Parameters.AddWithValue("@rfq_id", request.RFQID);
                    command.Parameters.AddWithValue("@customer_id", request.CustomerID);
                    command.Parameters.AddWithValue("@rfqdate", request.RFQDate);
                    command.Parameters.AddWithValue("@instructions", request.Instructions);
                    command.ExecuteNonQuery();
                }

                //execute the query2
                foreach (RFQ_ItemDetail item in RFQItemList)
                {
                    SqlCommand commands = new SqlCommand(query2, conn);
                    commands.Parameters.AddWithValue("@rfqID", item.RFQID);
                    commands.Parameters.AddWithValue("@itemID", item.ItemID);
                    commands.Parameters.AddWithValue("@quantity", item.Quantity);
                    commands.Parameters.AddWithValue("@unit_price", item.AskingPrice);
                    commands.ExecuteNonQuery();
                }
            }
            catch (SqlException exception)
            {
                MessageBox.Show(exception.Message);
            }
            conn.Close();
            //shown message when RFQ is saved inside the database
            MessageBox.Show("RFQ is successfully saved into Database.");
        }    

        //method to read the RFQ inside the database that are saved before
        public static ArrayList ReadSavedRFQ()
        {            
            int rfq_id = 0;
            int customer_id = 0;
            string rfq_date = "";
            string instructions = "";
            //arraylist to save the rfq inside the database
            ArrayList savedRFQ = new ArrayList();
            try
            {
                conn.ConnectionString = DBCONNSTR;
                conn.Open();
                comm.Connection = conn;
                //command to select every data inside RFQ Table
                comm.CommandText = "Select * From RFQ";
                SqlDataReader reader = comm.ExecuteReader();

                //read the data by executing sqldatareader
                while (reader.Read())
                {
                    //insert the data into local variable
                    rfq_id = Convert.ToInt32(reader[0].ToString());
                    customer_id = Convert.ToInt32(reader[1].ToString());
                    rfq_date = reader[2].ToString();
                    instructions = reader[3].ToString();

                    //insert data into RFQ object
                    RFQ saved_rfq = new RFQ(rfq_id, customer_id, rfq_date, instructions);
                    //insert RFQ object into arraylist with its data
                    savedRFQ.Add(saved_rfq);
                }
            }
            catch (Exception exception)
            {
                MessageBox.Show(exception.Message);
            }
            conn.Close();
            //return the saved rfq list inside the savedrfq arraylist
            return savedRFQ;
        }

        //method to read the rfq detail based on the searched rfqid
        public static ArrayList ReadSearchedRFQ(int rfq_id)
        {
            int customer_id = 0;
            string rfq_date = "";
            string instructions = "";
            //arraylist to save the rfq inside the database
            ArrayList savedRFQ = new ArrayList();
            try
            {
                conn.ConnectionString = DBCONNSTR;
                conn.Open();
                comm.Connection = conn;
                //command to select every data inside RFQ Table based on the rfqid
                comm.CommandText = "Select * From RFQ where RFQID=@searchedid";
                comm.Parameters.AddWithValue("@searchedid", rfq_id);
                SqlDataReader reader = comm.ExecuteReader();

                //read the data by executing sqldatareader
                while (reader.Read())
                {
                    //insert the data into local variable
                    customer_id = Convert.ToInt32(reader[1].ToString());
                    rfq_date = reader[2].ToString();
                    instructions = reader[3].ToString();

                    //insert RFQ object into arraylist with its data
                    RFQ saved_rfq = new RFQ(customer_id, rfq_date, instructions);
                    savedRFQ.Add(saved_rfq);
                }
                reader.Close();
                //clear the paarameter values inside query
                comm.Parameters.Clear();
            }
            catch (Exception exception)
            {
                MessageBox.Show(exception.Message);
            }
            conn.Close();
            //return the saved rfq list inside the savedrfq arraylist
            return savedRFQ;
        }

        //method to read the rfqdetail based on user seleted row
        public static ArrayList ReadSelectedRFQDetail(int rfq_id)
        {
            string item_id = "";
            int quantity = 0;
            double asking_price = 0.0;
            ArrayList item_list = new ArrayList();
            try
            {
                conn.ConnectionString = DBCONNSTR;
                conn.Open();
                comm.Connection = conn;
                //command text to select item detail based on RFQID
                comm.CommandText = "Select ItemID, Quantity, AskingPrice From RFQDetail Where RFQID=@id;";
                //add parameter value
                comm.Parameters.AddWithValue("@id", rfq_id);
                SqlDataReader reader = comm.ExecuteReader();

                //read the data by executing sqldatareader
                while (reader.Read())
                {
                    //insert the data into local variable
                    item_id = reader[0].ToString();
                    quantity = Convert.ToInt32(reader[1].ToString());
                    asking_price = Convert.ToDouble(reader[2].ToString());

                    //insert RFQ item detail object into arraylist with its data
                    RFQ_ItemDetail item_detail = new RFQ_ItemDetail(item_id, quantity, asking_price);
                    item_list.Add(item_detail);
                }
                reader.Close();
                //clear the paarameter values inside query
                comm.Parameters.Clear();
            }
            catch (Exception exception)
            {
                MessageBox.Show(exception.Message);
            }
            conn.Close();
            //return the item list inside the item_list
            return item_list;
        }

        //method to fill item detail to generate quotation
        public static ArrayList FillItemDetail(int rfq_id)
        {
            string id = "";
            double unitprice = 0.0;
            int quantity = 0;
            ArrayList quotation_item_list = new ArrayList();
            try
            {
                conn.ConnectionString = DBCONNSTR;
                conn.Open();
                comm.Connection = conn;
                //command text to select product detail based on RFQID and item id
                comm.CommandText = "Select r.ItemID, p.UnitPrice,r.Quantity From Product p, RFQDetail r Where p.ProductID=r.ItemID And r.RFQID=@rfqid;";
                //add parameter value
                comm.Parameters.AddWithValue("@rfqid", rfq_id);
                SqlDataReader reader = comm.ExecuteReader();

                //read the data by executing sqldatareader
                while (reader.Read())
                {
                    //insert the data into local variable
                    id = reader[0].ToString();
                    unitprice = Convert.ToDouble(reader["UnitPrice"].ToString());
                    quantity = Convert.ToInt32(reader["Quantity"].ToString());

                    //insert Product object into arraylist with its data
                    Product quotation_product = new Product(id, unitprice, quantity);
                    quotation_item_list.Add(quotation_product);
                }
                comm.Parameters.Clear();
            }
            catch (Exception exception)
            {
                MessageBox.Show(exception.Message);
            }
            conn.Close();
            //return the product detail inside the quotation_item_list
            return quotation_item_list;
        }

        //method to call validation error list
        public static void ValidationCallBack(Object sender, ValidationEventArgs args)
        {
            checkValid = false; 
            //call validation error list by adding eventargs message
            AllFormClass.Validation_Error.Add(args.Message);
        }

        //method to validate the generate xml
        public static bool validateXml(String filename)
        {
            //declare xmlreader and read the file
            XmlTextReader xmlreader = new XmlTextReader(filename);
            //declare xmlvalidating reader read the file
            XmlValidatingReader xmlvread = new XmlValidatingReader(xmlreader);            
                        
            xmlvread.ValidationEventHandler += new ValidationEventHandler(ValidationCallBack);
            checkValid = true; 
            //read xml file
            while (xmlvread.Read()) { }           
            //close the reader
            xmlvread.Close();
            //return the result of validating
            return checkValid;
        }
        //public static void ChangeRFQStatus(int rfq_id)
        //{
        //    try
        //    {
        //        conn.ConnectionString = DBCONNSTR;
        //        conn.Open();
        //        comm.Connection = conn;
        //        string sent_status = "Replied";

        //        string query1 = "insert into RFQ (Status) values (@status) Where RFQID=@rfq_id";
        //        SqlCommand command = new SqlCommand(query1, conn);
        //        command.Parameters.AddWithValue("@status", sent_status);
        //        command.Parameters.AddWithValue("@rfq_id", rfq_id);
        //        command.ExecuteNonQuery();
        //    }
        //    catch (SqlException exception)
        //    {
        //        MessageBox.Show(exception.Message);
        //    }
        //    conn.Close();
        //}

        //public static Boolean checkexistingRFQ(int rfq_id)
        //{
        //    int read_rfq_id=0;
        //    bool sameresult = false; 
        //    ArrayList savedRFQ = new ArrayList();
        //    try
        //    {
        //        conn.ConnectionString = DBCONNSTR;
        //        conn.Open();
        //        comm.Connection = conn;
        //        comm.CommandText = "Select RFQID From RFQ";
        //        SqlDataReader reader = comm.ExecuteReader();

        //        while (reader.Read())
        //        {
        //            read_rfq_id = Convert.ToInt32(reader[0].ToString());
        //        }

        //        if (read_rfq_id == rfq_id)
        //        {
        //            sameresult = true;
        //        }
        //        else
        //        {
        //            sameresult = false;
        //        }                
        //    }
        //    catch (Exception exception)
        //    {
        //        MessageBox.Show(exception.Message);
        //    }
        //    conn.Close();
        //    return sameresult;
        //}
    }
}
